# import re
#
# import mysql.connector
# import pandas as pd
# from pypinyin import lazy_pinyin
#
# excel_path = ''
# table_name = ''
#
# # 自定义数据类型映射（可选）
# data_types = {
#     'column1': 'INT',  # 整型
#     'column2': 'DATE',  # 日期
#     'column3': 'DECIMAL(10,2)',  # 数字
# }
# host = 'localhost'
# port = '3307'
# user = 'root'
# password = '123456'
# database = 'cmxx'
# # 定义数据库连接参数
#
# db_config = {
#     'host': host,
#     'port': port,  # 默认为3306，如果未指定可省略此键
#     'user': user,
#     'password': password,
#     'database': database
# }
#
#
# def input_with_default(question, default):
#     answer = input(question + " (默认值是：'%s'): " % default)
#     return default if answer == '' else answer
#
#
# class excel_to_mysql:
#     def __init__(self, if_not_exists=True):
#         self.cursor = None
#         self.result = None
#         self.excel_path = excel_path
#         self.table_name = table_name
#         self.if_not_exists = if_not_exists
#         self.db_config = db_config
#         self.data_types_map = data_types
#         self.create_table_sql = self.generate_mysql_create_table_sql()
#         self.mysql_conn = None
#
#     # 判断是否全是汉字如果是返回拼音，否则返回原字符串
#
#     def chinese_to_pinyin(text=''):
#         return_string = []
#         pattern = r'[^\u4e00-\u9fa5]'
#
#         for char in re.sub(pattern, '', text):
#             if '\u4e00' <= char <= '\u9fa5':
#                 return_string.append(lazy_pinyin(char)[0])
#             else:
#                 return_string.append(char)
#
#         return "_".join(return_string)
#
#     # def generate_mysql_create_table_sql(self):
#     #     # 读取Excel文件并获取表头
#     #     df = pd.read_excel(self.excel_path, header=0, nrows=0)
#     #     columns = df.columns.tolist()
#     #
#     #     # 构建列定义字符串
#     #     column_definitions = []
#     #     for column_name in columns:
#     #         custom_type = self.data_types_map.get(column_name)
#     #         data_type = custom_type if custom_type is not None else 'VARCHAR(255)'
#     #
#     #         column_definitions.append(f"{column_name} {data_type}  \n \t ")
#     #
#     #     # 构建完整的建表语句
#     #     if_not_exists_clause = 'IF NOT EXISTS' if self.if_not_exists else ''
#     #     create_table_sql = f"CREATE TABLE  {if_not_exists_clause}  `{self.table_name}`   \n ( {','.join(column_definitions)}\n);"
#     #     return create_table_sql
#
#     def connect_mysql(self):
#         self.mysql_conn = mysql.connector.connect(**self.db_config)
#         self.cursor = self.mysql_conn.cursor()
#
#     def execute_query(self, query):
#         """
#         执行SQL查询语句。
#         """
#         self.cursor.execute(query)
#         self.result = self.cursor.fetchall()
#         return self.result
#
#     def execute_query_params(self, query, params):
#         """
#         执行SQL查询语句。
#         """
#         self.cursor.execute(query, params)
#         self.cursor.commit()  # 提交事务
#
#     def close_mysql(self):
#         """
#         关闭MySQL数据库连接。
#         """
#         self.cursor.close()
#         self.mysql_conn.close()
#
#     def create_table(self):
#         """
#         创建MySQL表。
#         """
#         self.connect_mysql()
#         self.execute_query(self.create_table_sql)
#         self.close_mysql()
#
#     def read_excel(self):
#         """
#         读取指定Excel文件并返回DataFrame。
#
#         返回:
#         - pandas DataFrame: 包含Excel数据的DataFrame对象
#         """
#         return pd.read_excel(self.excel_path)
#
#     def write_to_database(self, dataframe, table_name, if_exists='replace'):
#         """
#         将DataFrame中的数据写入MySQL数据库的指定表。
#
#         参数:
#         - dataframe (pandas DataFrame): 包含待写入数据的DataFrame
#         - table_name (str): 目标数据库表名
#         - if_exists (str, optional): 指定在目标表已存在时的行为，可选值包括：
#           - 'replace': 删除现有表并重新创建（默认）
#           - 'append': 在已有数据后追加新数据
#           - 'fail': 如果表已存在，则引发异常
#
#         异常:
#         - Raises mysql.connector.Error if a database error occurs.
#         """
#         columns = ', '.join(dataframe.columns.tolist())
#
#         values_placeholders = ', '.join(['%s'] * len(dataframe.columns))
#
#         insert_data_sql = f"INSERT INTO {table_name} VALUES ({values_placeholders})"
#
#         for index, row in dataframe.iterrows():
#             row_data = tuple(row.values)
#             self.connection_manager.executeNonQuery(insert_data_sql, row_data)
#
#     def process_excel_to_db(self, table_name, if_exists='replace'):
#
#         dataframe = self.read_excel()
#         self.write_to_database(dataframe, table_name, if_exists=if_exists)
#
#
# # 主循环，模拟交互式运维会话
# while True:
#     if not excel_path:
#         excel_path = input_with_default("请输入Excel文件路径,如:F:/sql.xlsx ", 'F:/sql.xlsx ')
#         table_name = input_with_default("请输入数据库表名,如:target_table1 ", 'table_name')
#         host = input_with_default("请输入数据地址——如: 127.0.0.1 ", '127.0.0.1')
#         port = input_with_default("请输入数据端口——如: 3306 ", 3306)
#         user = input_with_default("请输入数据用户名——如: user ", 'user')
#         password = input_with_default("请输入数据密码——如: passowrd ", 'passowrd')
#         database = input_with_default("请输入数据库名——如: mysql ", 'mysql')
#
#     print("数据库连接参数:%s" % db_config)
#
#     choice = input_with_default("Enter your choice (1-4): ", '1')
#
#     if choice == '1':
#         db = excel_to_mysql()
#         db.connect_mysql()
#         db.create_table()
#
#     elif choice == '2':
#
#     elif choice == '3':
#         break
#     elif choice == '4':
#         break
#     else:
#         print("Invalid choice. Please enter a number between 1 and 4.")
#     print("\n--- Press Enter to continue ---")
